﻿using System;
using System.Collections.Generic;
using System.Text;
using GFrame.Data;

namespace EZCRS.HCMS.DataAcl
{
    public class InventoryPlanDAO:IDisposable
    {

        private SQLReader _sr = new SQLReader();
        private const string CONNECTION_STRING_NAME = "mainDB";

        public object getPlanMinStartDate(int PID, string RoomCode, string PlanCode)
        {
            ParamList pl = new ParamList();

            pl.Add("@v_RoomCode", System.Data.SqlDbType.VarChar, RoomCode);
            pl.Add("@v_PlanCode", System.Data.SqlDbType.VarChar, PlanCode);
            pl.Add("@i_PID", System.Data.SqlDbType.Int, PID);

            string strSQL = "SELECT min(StartDate) FROM HCRS_InventoryPlan WHERE ";
            
            strSQL += "PID = @i_PID AND RoomCode = @v_RoomCode AND PlanCode = @v_PlanCode";

            object obj = _sr.ExecuteScalarCommand(strSQL, CONNECTION_STRING_NAME, pl);

            return obj;
        }

        public bool InDateRange(int PID, string RoomCode, string PlanCode, DateTime StartDate1, DateTime StartDate2)
        {
            ParamList pl = new ParamList();

            pl.Add("@v_RoomCode", System.Data.SqlDbType.VarChar, RoomCode);
            pl.Add("@v_PlanCode", System.Data.SqlDbType.VarChar, PlanCode);
            pl.Add("@dt_Start", System.Data.SqlDbType.DateTime, StartDate1);
            pl.Add("@dt_End", System.Data.SqlDbType.DateTime, StartDate2);

            pl.Add("@i_PID", System.Data.SqlDbType.Int, PID);

            string strSQL = "SELECT ID FROM HCRS_InventoryPlan WHERE ";

            strSQL += "PID = @i_PID AND RoomCode = @v_RoomCode AND PlanCode = @v_PlanCode AND ((StartDate < @dt_Start AND EndDate < @dt_End) OR (StartDate > @dt_Start AND EndDate > @dt_End))";

            object obj = _sr.ExecuteScalarCommand(strSQL, CONNECTION_STRING_NAME, pl);



            if (obj != DBNull.Value && obj != null)
            {
                int i = 0;
                int.TryParse(obj.ToString(), out i);

                if (i > 0)
                    return false;
                else
                    return true;
            }

            return false;
        }

        public object getPlanMaxEndDate(int PID, string RoomCode, string PlanCode)
        {
            ParamList pl = new ParamList();

            pl.Add("@v_RoomCode", System.Data.SqlDbType.VarChar, RoomCode);
            pl.Add("@v_PlanCode", System.Data.SqlDbType.VarChar, PlanCode);

            pl.Add("@i_PID", System.Data.SqlDbType.Int, PID);

            string strSQL = "SELECT max(EndDate) FROM HCRS_InventoryPlan WHERE ";

            strSQL += "PID = @i_PID AND RoomCode = @v_RoomCode AND PlanCode = @v_PlanCode";

            object obj = _sr.ExecuteScalarCommand(strSQL, CONNECTION_STRING_NAME, pl);

            return obj;
        }

        #region IDisposable Members

        public void Dispose()
        {
            _sr.Dispose();
        }

        #endregion
    }
}
